package persistance.userDB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import metier.entite.user.TypeUser;
import metier.entite.user.User;
import metier.sercurity.PasswordEncryptor;
import stegApplication.Configuration;


public class UserDB {

	private static final String USER_DB = "jdbc:sqlite:DataBase/user.db" ;
	
	public static void createTableUser() throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		// database path, if it's new database,
		// it will be created in the project folder
		Connection con = DriverManager.getConnection(USER_DB);
		Statement stat = con.createStatement();
		stat.executeUpdate("drop table if exists user");
		
		//creating table
		stat.executeUpdate("create table user(id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "username varchar(30)," 
				+ "password varchar(100)," 
				+ "type varchar(15));");
		con.close() ;
	}
	
	public static void addUser(User user)throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		Connection con = DriverManager.getConnection(USER_DB);
		PreparedStatement prep = con.prepareStatement("insert into user values(?,?,?,?);");
		prep.setString(2, user.getUserName());
		prep.setString(3,user.getPassword());
		prep.setString(4, user.getType().toString());
		prep.execute();
		con.close() ;
	}
	
	public static boolean  removeUser(User user) throws ClassNotFoundException, SQLException {
		if (isUserExistant(user)){
			Class.forName("org.sqlite.JDBC");
			Connection con = DriverManager.getConnection(USER_DB);
			Statement stat = con.createStatement();
			stat.executeUpdate("delete from user where " +
					"username=\'" + user.getUserName()+"\'  and " +
					"type=\'" + user.getType().toString()+"\' ;" );
			con.close() ;
			return true ;
		} else {
			return false ;
		}
	}
	
	public static boolean isUserExistant(User user) throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		Connection con = DriverManager.getConnection(USER_DB);
		Statement stat = con.createStatement();
		ResultSet res = stat.executeQuery("select id from user where " +
				"username=\'" + user.getUserName()+"\' and " +
				"type=\'" + user.getType().toString()+"\' ;" );
		if (res.next()){
			res.close() ;
			con.close() ;
			return true ;
		} else {
			res.close() ;
			con.close() ;
			return false ;
		}
	}
	
	/**
	 * 	 Update User
	 * @param oldUser The user to be updated, exists already in DataBase
	 * @param newUser New user (Password should be plain : Should set attribute plainPassword) 
	 * @return true if user is well updated <br> false if user do not exist
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public static boolean updateUser(User newUser) throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		Connection con = DriverManager.getConnection(USER_DB);
		Statement stat = con.createStatement();
		stat.executeUpdate("update user set  " +
				"username=\'" + newUser.getUserName()+"\' , " +
				"type=\'" + newUser.getType().toString()+"\' , " +
				"password=\'" +  newUser.getPassword()+"\' " +
				"where " +
				"id=\'" + newUser.getId()+"\';");
		con.close() ;
		return true ;
	}
	
	public static List<User> getUserClient()  throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		Connection con = DriverManager.getConnection(USER_DB);
		Statement stat = con.createStatement();
		// getting data
		ArrayList<User> listE = new ArrayList<User>() ;
		ResultSet res = stat.executeQuery("select * from user where type=\'"+TypeUser.CLIENT.toString() +"\';");
		while (res.next()) {
			if (Configuration.MODE_DEBUG){
				System.out.println("-------------------"
						+"\nid="+res.getInt("id") 
						+"\nuserName="+res.getString("username") 
						+ "\nencrupted Password=" + res.getString("password") 
						+ "\ndecrypted Password=" + PasswordEncryptor.decode(res.getString("password"))
						+ "\nType=" + res.getString("type"));
			}
			listE.add(new User(res.getInt("id"),res.getString("username") , res.getString("password") ,TypeUser.fromString(res.getString("type"))));
		}
		res.close() ;
		con.close() ;
		return listE ;
	}
	
	public static List<User> getAllUsers()  throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		Connection con = DriverManager.getConnection(USER_DB);
		Statement stat = con.createStatement();
		// getting data
		ArrayList<User> listE = new ArrayList<User>() ;
		ResultSet res = stat.executeQuery("select * from user ;");
		while (res.next()) {
			if (Configuration.MODE_DEBUG){
			System.out.println("-------------------"
					+"\nid="+res.getInt("id") 
					+"\nuserName="+res.getString("username") 
					+ "\nencrupted Password=" + res.getString("password") 
					+ "\ndecrypted Password=" + PasswordEncryptor.decode(res.getString("password"))
					+ "\nType=" + res.getString("type"));
			}
			listE.add(new User(res.getInt("id"),res.getString("username") , res.getString("password"),TypeUser.fromString(res.getString("type"))));
		}
		res.close() ;
		con.close() ;
		return listE ;
	}
	
	public static User getUserByUserName(String userName){
		// TODO
		return null ;
	}
	
	public static User connectUser(String userName , char[] password) throws ClassNotFoundException, SQLException {
		User returnedUser = null  ;
		Class.forName("org.sqlite.JDBC");
		Connection con = DriverManager.getConnection(USER_DB);
		Statement stat = con.createStatement();
		ResultSet res = stat.executeQuery("select * from user where username=\'"+userName+"\'");
		while (res.next()) {
			if ( Arrays.equals(PasswordEncryptor.decode(res.getString("password")).toCharArray(), password)){
				returnedUser = new User(res.getString("username"), PasswordEncryptor.encode(res.getString("password")),TypeUser.fromString(res.getString("type"))) ;
			}
		}
		res.close() ;
		con.close() ;
		return returnedUser ;
	}

	public static User getUserById(int id) throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		Connection con = DriverManager.getConnection(USER_DB);
		Statement stat = con.createStatement();
		// getting data
		ResultSet res = stat.executeQuery("select * from user where id=\'"+id+"\';");
		User user = null ;
		while (res.next()) {
			if (Configuration.MODE_DEBUG){
			System.out.println("-------------------"
					+"\nid="+res.getInt("id") 
					+"\nuserName="+res.getString("username") 
					+ "\nencrupted Password=" + res.getString("password") 
					+ "\ndecrypted Password=" + PasswordEncryptor.decode(res.getString("password"))
					+ "\nType=" + res.getString("type"));
			}
			user= new User(res.getInt("id"),res.getString("username") , res.getString("password") ,TypeUser.fromString(res.getString("type")));
		}
		res.close() ;
		con.close() ;
		return user ;
	}
}
